from backend.app.app.api.api_Xiong.crud.pub_crud import dvpunit_name_tree, stimtype_tree
from backend.app.app.db.session_project import session_pro
from backend.app.app.db.session_public import session_pub
import backend.app.app.models.db_owi_private_models as md_pro
import backend.app.app.models.db_owi_public_models as md_pub


#   读取评价计算结果
def get_eval_result():
    try:
        datas = session_pro.query(md_pro.TbUserprojDvpunitOilstimevalOut).all()
        result = [data.__dict__ for data in datas]
        return result
    except Exception as e:
        print(e, e.__traceback__.tb_lineno)
        return None


# 获取所有输出数据信息
def get_all_data(query_data):
    try:
        datas = session_pub.query(md_pub.TbYearDvpunitOilstimulateEvaluateResult).filter(
            md_pub.TbYearDvpunitOilstimulateEvaluateResult.DvpUnit_Name == query_data['name'],
            md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimType_Name == query_data['type'],
            md_pub.TbYearDvpunitOilstimulateEvaluateResult.Year <= query_data['up_year'],
            md_pub.TbYearDvpunitOilstimulateEvaluateResult.Year >= query_data['low_year']
        ).order_by(md_pub.TbYearDvpunitOilstimulateEvaluateResult.Year).all()
        result = [data.__dict__ for data in datas]
        return result
    except Exception as e:
        print(e, e.__traceback__.tb_lineno)
        return None

def create_tree(total_data, parent_unit_name, data_type):
    if data_type == "开发单元":
        belong_str, id_str, name_str, rank_str = "Belong_DvpUnit_Name", "DvpUnit_ID", "DvpUnit_Name", "DvpUnit_Rank"
    elif data_type == "措施":
        belong_str, id_str, name_str, rank_str = "Belong_StimType_Name", "StimType_ID", "StimType_Name", "StimType_Rank"
    tree = []
    try:
        for data in total_data:
            if data[rank_str] == "1":
                continue
            temp_dic = dict()
            if data[belong_str] == parent_unit_name:
                temp_dic['id'] = data[id_str]
                temp_dic['title'] = data[name_str]
                temp_dic['rank'] = data[rank_str]
                children = create_tree(total_data, data[name_str], data_type)
                if children:
                    temp_dic['children'] = children
                tree.append(temp_dic)
        return tree
    except Exception as e:
        print(e, e.__traceback__.tb_lineno)


# 获取所有单元名称和措施类别，以及年份，按年份排序
def get_name_year():
    try:
        units = session_pub.query(
            md_pub.TbDvpunitbaisc.DvpUnit_Name,
            md_pub.TbDvpunitbaisc.Belong_DvpUnit_Name,
            md_pub.TbDvpunitbaisc.DvpUnit_ID,
            md_pub.TbDvpunitbaisc.DvpUnit_Rank
        ).filter(
            md_pub.TbDvpunitbaisc.DvpUnit_Name.in_(
                [unit[0] for unit in
                 session_pub.query(
                     md_pub.TbYearDvpunitOilstimulateEvaluateResult.DvpUnit_Name
                 ).all()
                 ]
            )
        ).all()
        units = [dict(zip(unit.keys(), unit)) for unit in units]
        # 处理单元数据，生成树形结构
        dvp_tree = list()
        dvp_list = sorted(units, key=lambda x: x["DvpUnit_Rank"])
        while len(dvp_list):
            item = dvp_list[0]
            temp_dict = dict()
            temp_dict.update({
                'id': item['DvpUnit_ID'],
                'title': item['DvpUnit_Name'],
            })
            child = dvpunit_name_tree(dvp_list, item['DvpUnit_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            dvp_tree.append(temp_dict)
            del_name = set()
            new_list = list()
            del_name.add(item['DvpUnit_Name'])
            for x in dvp_list:
                if x['Belong_DvpUnit_Name'] in del_name or x['DvpUnit_Name'] in del_name:
                    del_name.add(x['DvpUnit_Name'])
                else:
                    new_list.append(x)
            dvp_list = new_list

        # print("now", units)
        stim_type = session_pub.query(
            md_pub.TbOilwellstimType.StimType_Name,
            md_pub.TbOilwellstimType.Belong_StimType_Name,
            md_pub.TbOilwellstimType.StimType_Rank,
            md_pub.TbOilwellstimType.StimType_ID
        ).filter(
            md_pub.TbOilwellstimType.StimType_Name.in_(
                [stim[0] for stim in
                 session_pub.query(
                     md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimType_Name
                 ).all()
                 ]
            )
        ).all()
        stim_type = [dict(zip(type.keys(), type)) for type in stim_type]

        # 处理措施数据，生成树形结构
        stim_type_tree = list()
        stimtype_list = sorted(stim_type, key=lambda x: x["StimType_Rank"])
        while len(stimtype_list):
            item = stimtype_list[0]
            temp_dict = dict()
            temp_dict.update({
                'id': item['StimType_ID'],
                'title': item['StimType_Name'],
            })
            child = stimtype_tree(stimtype_list, item['StimType_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            stim_type_tree.append(temp_dict)
            del_name = set()
            new_list = list()
            del_name.add(item['StimType_Name'])
            for x in stimtype_list:
                if x['Belong_StimType_Name'] in del_name or x['StimType_Name'] in del_name:
                    del_name.add(x['StimType_Name'])
                else:
                    new_list.append(x)
            stimtype_list = new_list

        years = session_pub.query(
            md_pub.TbYearDvpunitOilstimulateEvaluateResult.Year
        ).distinct().all()

        # 获取年份数据
        year_list = list()
        if len(years):
            for year in years:
                year_list.append(year[0])

        response_data = {
            # "DvpUnit": units_tree if units_tree else None,
            "DvpUnit": dvp_tree,
            "StimType": stim_type_tree,
            "Year": year_list if year_list else None
        }

        return response_data
    except Exception as e:
        session_pub.rollback()
        print(e, e.__traceback__.tb_lineno)
        return (e.__traceback__.tb_lineno)


# # 更新数据
# def out_updata(out_upd: dict):
#     try:
#         session_pro.query(md_pro.TbUserprojDvpunitOilstimevalOut). \
#             filter(md_pro.TbUserprojDvpunitOilstimevalOut.User_ID == out_upd["User_ID"],
#                    md_pro.TbUserprojDvpunitOilstimevalOut.Proj_ID == out_upd["Proj_ID"],
#                    md_pro.TbUserprojDvpunitOilstimevalOut.DvpUnit_Name == out_upd["DvpUnit_Name"]) \
#             .update(
#             {
#                 md_pro.TbUserprojDvpunitOilstimevalOut.Year: out_upd["Year"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimType_Name: out_upd["StimType_Name"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.Stim_WellNumber_Valid: out_upd["Stim_WellNumber_Valid"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.Stim_WellValidRatio: out_upd["Stim_WellValidRatio"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.Stim_ValidPeriod_Average: out_upd["Stim_ValidPeriod_Average"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.Stim_IncreOil_Average: out_upd["Stim_IncreOil_Average"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimOil_CommodityRatio: out_upd["StimOil_CommodityRatio"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.Oil_Price: out_upd["Oil_Price"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.OilSale_TotalTaxRatio: out_upd["OilSale_TotalTaxRatio"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.Money_Discount: out_upd["Money_Discount"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.Oil_OprCost: out_upd["Oil_OprCost"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimCost_Average: out_upd["StimCost_Average"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimOil_WellOprCost_Average: out_upd[
#                     "StimOil_WellOprCost_Average"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimOil_Total_Tax_Average: out_upd["StimOil_Total_Tax_Average"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimOil_Gross_SalesIncome_Average: out_upd[
#                     "StimOil_Gross_SalesIncome_Average"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimOil_Static_Gains_Average: out_upd[
#                     "StimOil_Static_Gains_Average"],  # Stim__IncreOil_Critical_Average
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimOil_NPV_Average: out_upd["StimOil_NPV_Average"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimOil_ROI: out_upd["StimOil_ROI"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimOil_NPVR: out_upd["StimOil_NPVR"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimOil_FIRR: out_upd["StimOil_FIRR"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.Stim_IncreOil_Critical_Average: out_upd[
#                     "Stim_IncreOil_Critical_Average"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.StimOil_Price_Critical: out_upd["StimOil_Price_Critical"],
#                 md_pro.TbUserprojDvpunitOilstimevalOut.Remark: out_upd["Remark"]
#             }
#         )
#         session_pro.commit()
#         print('更新输入数据成功')
#         return True
#     except:
#         print('更新输入数据失败')
#         session_pro.rollback()
#         return False


# 存库删除部分数据 hzc改
def out_del(delete_data):
    result = list()
    for data in delete_data:
        try:
            session_pub.query(md_pub.TbYearDvpunitOilstimulateEvaluateResult).filter(
                md_pub.TbYearDvpunitOilstimulateEvaluateResult.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimType_Name == data['StimType_Name'],
                md_pub.TbYearDvpunitOilstimulateEvaluateResult.Year == data['Year']).delete()
            session_pub.commit()
            result.append(
                {
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'StimType_Name': data['StimType_Name'],
                    'Year': data['Year'],
                    'result': True
                }
            )
        except Exception as e:
            result.append(
                {
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'StimType_Name': data['StimType_Name'],
                    'Year': data['Year'],
                    'result': False
                }
            )
            print(e)
            session_pub.rollback()
    return result


# 将所有信息存库
"""
user_id,pro_id,单元名称，年度，措施类别，有效井次，有效率，平均有效期，平均措施增油，8
    平均油价，原油商品率，平均销售税费率,资金贴现率，操作吨油成本,平均作业成本，备注   15
    平均增油操作成本,平均销售税费，平均增油毛收入，平均静态收益，平均财务净现值，投资利润率，财务净现值率，
    财务内部收益率，平均临界增油量，经济临界油价
"""


def out_data_add(out_data):
    # hzc改
    return_data = list()
    for data in out_data:
        try:
            is_existed = session_pub.query(
                md_pub.TbYearDvpunitOilstimulateEvaluateResult
            ).filter(
                md_pub.TbYearDvpunitOilstimulateEvaluateResult.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimulateEvaluateResult.Year == data['Year'],
                md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimType_Name == data['StimType_Name'],
            ).count()
            if is_existed:
                session_pub.query(
                    md_pub.TbYearDvpunitOilstimulateEvaluateResult
                ).filter(
                    md_pub.TbYearDvpunitOilstimulateEvaluateResult.DvpUnit_Name == data['DvpUnit_Name'],
                    md_pub.TbYearDvpunitOilstimulateEvaluateResult.Year == data['Year'],
                    md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimType_Name == data['StimType_Name'],
                ).update(
                    {
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.Stim_WellValidRatio: data['Stim_WellValidRatio'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.Stim_WellNumber_Valid: data[
                            'Stim_WellNumber_Valid'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.Stim_ValidPeriod_Average: data[
                            'Stim_ValidPeriod_Average'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.Stim_IncreOil_Average: data[
                            'Stim_IncreOil_Average'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimCost_Average: data['StimCost_Average'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimOil_CommodityRatio: data[
                            'StimOil_CommodityRatio'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimOil_Gross_SalesIncome_Average: data[
                            'StimOil_Gross_SalesIncome_Average'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimOil_Total_TaxRatio_Average: data[
                            'StimOil_Total_TaxRatio_Average'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimOil_Static_Gains_Average: data[
                            'StimOil_Static_Gains_Average'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimOil_NPV_Average: data['StimOil_NPV_Average'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimOil_ROI: data['StimOil_ROI'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimOil_NPVR: data['StimOil_NPVR'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimOil_FIRR: data['StimOil_FIRR'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.Stim_IncreOil_Critical_Average: data[
                            'Stim_IncreOil_Critical_Average'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.Remark: data['Remark'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.Oil_Price: data['Oil_Price'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.OilSale_TotalTaxRatio: data[
                            'OilSale_TotalTaxRatio'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.Money_Discount: data['Money_Discount'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.Oil_OprCost: data['Oil_OprCost'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimOil_Price_Critical: data[
                            'StimOil_Price_Critical'],
                        md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimOil_WellOprCost_Average: data[
                            'StimOil_WellOprCost_Average'],
                    }
                )
            else:
                add_data = md_pub.TbYearDvpunitOilstimulateEvaluateResult(
                    DvpUnit_Name=data['DvpUnit_Name'],
                    Year=data['Year'],
                    StimType_Name=data['StimType_Name'],
                    Stim_WellValidRatio=data['Stim_WellValidRatio'],
                    Stim_WellNumber_Valid=data['Stim_WellNumber_Valid'],
                    Stim_ValidPeriod_Average=data['Stim_ValidPeriod_Average'],
                    Stim_IncreOil_Average=data['Stim_IncreOil_Average'],
                    StimCost_Average=data['StimCost_Average'],
                    StimOil_CommodityRatio=data['StimOil_CommodityRatio'],
                    StimOil_Gross_SalesIncome_Average=data['StimOil_Gross_SalesIncome_Average'],
                    StimOil_Total_TaxRatio_Average=data['StimOil_Total_TaxRatio_Average'],
                    StimOil_Static_Gains_Average=data['StimOil_Static_Gains_Average'],
                    StimOil_NPV_Average=data['StimOil_NPV_Average'],
                    StimOil_ROI=data['StimOil_ROI'],
                    StimOil_NPVR=data['StimOil_NPVR'],
                    StimOil_FIRR=data['StimOil_FIRR'],
                    Stim_IncreOil_Critical_Average=data['Stim_IncreOil_Critical_Average'],
                    Remark=data['Remark'],
                    Oil_Price=data['Oil_Price'],
                    OilSale_TotalTaxRatio=data['OilSale_TotalTaxRatio'],
                    Money_Discount=data['Money_Discount'],
                    Oil_OprCost=data['Oil_OprCost'],
                    StimOil_Price_Critical=data['StimOil_Price_Critical'],
                    StimOil_WellOprCost_Average=data['StimOil_WellOprCost_Average']
                )
                session_pub.add(add_data)
            session_pub.commit()
            return_data.append({
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'StimType_Name': data['StimType_Name'],
                'result': True
            })

        except Exception as e:
            return_data.append({
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'StimType_Name': data['StimType_Name'],
                'result': False,
            })
            session_pub.rollback()
            print(e)
            continue
    return return_data


def well_out_data_add(out_data):
    # xwl改
    return_data = list()
    for data in out_data:
        try:
            is_existed = session_pub.query(
                md_pub.Tb_OilWellStimEvaluateResult
            ).filter(
                md_pub.Tb_OilWellStimEvaluateResult.Well_Name == data['Well_Name'],
                md_pub.Tb_OilWellStimEvaluateResult.Stim_Date == data['Stim_Date'],
                md_pub.Tb_OilWellStimEvaluateResult.StimType_Name == data['StimType_Name']
            ).count()
            if is_existed:
                session_pub.query(md_pub.Tb_OilWellStimEvaluateResult).filter(
                    md_pub.Tb_OilWellStimEvaluateResult.Well_Name == data['Well_Name'],
                    md_pub.Tb_OilWellStimEvaluateResult.Stim_Date == data['Stim_Date'],
                    md_pub.Tb_OilWellStimEvaluateResult.StimType_Name == data['StimType_Name']
                ).update(
                    {
                        md_pub.Tb_OilWellStimEvaluateResult.Stim_ValidPeriod: data['Stim_ValidPeriod'],
                        md_pub.Tb_OilWellStimEvaluateResult.Stim_IncreOil: data['Stim_IncreOil'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimCost: data['StimCost'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimOil_WellOprCost: data['StimOil_WellOprCost'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimOil_CommodityRatio: data['StimOil_CommodityRatio'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimOil_Gross_SalesIncome: data[
                            'StimOil_Gross_SalesIncome'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimOil_Total_Tax: data['StimOil_Total_Tax'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimOil_Static_Gains: data['StimOil_Static_Gains'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimOil_NPV: data['StimOil_NPV'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimOil_ROI: data['StimOil_ROI'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimOil_NPVR: data['StimOil_NPVR'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimOil_FIRR: data['StimOil_FIRR'],
                        md_pub.Tb_OilWellStimEvaluateResult.Stim_IncreOil_Critical: data['Stim_IncreOil_Critical'],
                        md_pub.Tb_OilWellStimEvaluateResult.StimOil_Price_Critical: data['StimOil_Price_Critical'],
                        md_pub.Tb_OilWellStimEvaluateResult.Oil_Price: data['Oil_Price'],
                        md_pub.Tb_OilWellStimEvaluateResult.OilSale_TotalTaxRatio: data['OilSale_TotalTaxRatio'],
                        md_pub.Tb_OilWellStimEvaluateResult.Money_Discount: data['Money_Discount'],
                        md_pub.Tb_OilWellStimEvaluateResult.Oil_OprCost: data['Oil_OprCost'],
                        md_pub.Tb_OilWellStimEvaluateResult.Remark: data['Remark']
                    }
                )
            else:
                add_data = md_pub.Tb_OilWellStimEvaluateResult(
                    Well_Name=data['Well_Name'],
                    Stim_Date=data['Stim_Date'],
                    StimType_Name=data['StimType_Name'],
                    Stim_ValidPeriod=data['Stim_ValidPeriod'],
                    Stim_IncreOil=data['Stim_IncreOil'],
                    StimCost=data['StimCost'],
                    StimOil_WellOprCost=data['StimOil_WellOprCost'],
                    StimOil_CommodityRatio=data['StimOil_CommodityRatio'],
                    StimOil_Gross_SalesIncome=data['StimOil_Gross_SalesIncome'],
                    StimOil_Total_Tax=data['StimOil_Total_Tax'],
                    StimOil_Static_Gains=data['StimOil_Static_Gains'],
                    StimOil_NPV=data['StimOil_NPV'],
                    StimOil_ROI=data['StimOil_ROI'],
                    StimOil_NPVR=data['StimOil_NPVR'],
                    StimOil_FIRR=data['StimOil_FIRR'],
                    Stim_IncreOil_Critical=data['Stim_IncreOil_Critical'],
                    StimOil_Price_Critical=data['StimOil_Price_Critical'],
                    Oil_Price=data['Oil_Price'],
                    OilSale_TotalTaxRatio=data['OilSale_TotalTaxRatio'],
                    Money_Discount=data['Money_Discount'],
                    Oil_OprCost=data['Oil_OprCost'],
                    Remark=data['Remark']
                )
                session_pub.add(add_data)
            session_pub.commit()
            return_data.append({
                'Well_Name': data['Well_Name'],
                'Stim_Date': data['Stim_Date'],
                'result': True
            })

        except Exception as e:
            return_data.append({
                'Well_Name': data['Well_Name'],
                'Stim_Date': data['Stim_Date'],
                'result': False,
            })
            print(e)
            continue
    return return_data
